﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using YKhoa_Lib.BLL;

namespace YKhoa_Lib.DAL
{
    public class DAL_SoGoiKham : SqlConnectionRun
    {
        List<SqlParameter> parameters = null;
        public DataTable LayDanhSachSoGoiKham(string _maNV)
        {
            string sql = @"SELECT MaGK, TenGoiKham FROM Vienphi..So_GoiKham WHERE MaNV=@MaNV";
            parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("MaNV", _maNV));            
            DataTable dt = SqlExecuteQueryReader(sql, parameters);
            return dt;
        }

        public DataTable LayChiTietGoiKham(string _maGK)
        {
            string sql = @"SELECT ctgk.MaDV, ctgk.TenDichVu, dv.ThuPhi, khoa.MaKhoa FROM Vienphi..ChiTiet_GoiKham ctgk
                            LEFT JOIN Vienphi..DM_DichVu dv on ctgk.MaDV COLLATE DATABASE_DEFAULT = dv.MaDV COLLATE DATABASE_DEFAULT
                            LEFT JOIN Vienphi..DM_KHOA khoa on khoa.MaKhoa = dv.maKhoa
                            WHERE ctgk.MaGK=@MaGK";
            parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("MaGK", _maGK));
            DataTable dt = SqlExecuteQueryReader(sql, parameters);
            return dt;
        }

        public bool LuuSoGoiKham(So_GoiKham _obj)
        {
            sqlConnection.Open();
            SqlTransaction transaction = sqlConnection.BeginTransaction();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Transaction = transaction;
            try
            {
                //save to So_GoiKham
                sqlCommand.CommandText = @"INSERT INTO Vienphi..So_GoiKham(TenGoiKham, MaNV) VALUES(N'" + _obj.TenGoiKham + "', '" + _obj.MaNV + "');";
                sqlCommand.ExecuteNonQuery();
                //get magk
                sqlCommand.CommandText = @"SELECT IDENT_CURRENT('Vienphi..So_GoiKham')";
                object _idGK = sqlCommand.ExecuteScalar();
                //save to ChiTiet_GoiKham
                string sqlCTGK = @"INSERT INTO Vienphi..ChiTiet_GoiKham(MaGK, MaDV, TenDichVu) VALUES";
                foreach(ChiTiet_GoiKham CTGK in _obj.ChiTietGoiKhams)
                {
                    sqlCTGK += "('" + int.Parse(_idGK.ToString()) + "', '" + CTGK.MaDV + "', N'" + CTGK.TenDichVu + "'), ";
                }
                sqlCTGK = sqlCTGK.TrimEnd(',', ' ');
                sqlCommand.CommandText = sqlCTGK;
                int numRS = sqlCommand.ExecuteNonQuery();
                transaction.Commit();
                return numRS > 0;
            }
            catch(Exception e){
                transaction.Rollback();
                return false;
            }
            finally{
                sqlConnection.Close();
                sqlCommand.Dispose();
                transaction.Dispose();
            }
        }
    }
}